
package com.cari.baju.yog.database;

import java.util.ArrayList;
import java.util.Collections;
import java.util.Comparator;
import java.util.List;

import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.location.Location;
import android.text.TextUtils;

import com.cari.baju.yog.entity.E_Category;
import com.cari.baju.yog.entity.E_Toko;
import com.cari.baju.yog.utils.Utils;
import com.google.android.gms.maps.model.LatLng;
import com.readystatesoftware.sqliteasset.SQLiteAssetHelper;

public class DatabaseHelper extends SQLiteAssetHelper {

    private static final String DB_NAME = "db_caribajuyog.sqlite";
    private static final int DB_VERSION = 1;

    private static DatabaseHelper instance;
    private static SQLiteDatabase db;

    private static final String TB_CATEGORY = "tb_category";
    private static final String TB_CATEGORY_ID = "id";
    private static final String TB_CATEGORY_NAME = "name";

    private static final String TB_TOKO = "tb_location";
    private static final String TB_TOKO_ID = "id";
    private static final String TB_TOKO_NAME = "name";
    private static final String TB_TOKO_CATEGORY = "category_id";
    private static final String TB_TOKO_ADDRESS = "address";
    private static final String TB_TOKO_PHONE = "phone";
    private static final String TB_TOKO_LAT = "lat";
    private static final String TB_TOKO_LNG = "lng";

    public DatabaseHelper(Context context) {
        super(context, DB_NAME, null, DB_VERSION);
    }

    public static DatabaseHelper getInstance(Context context) {
        if (instance == null) {
            instance = new DatabaseHelper(context);
            db = instance.getWritableDatabase();
        }
        return instance;
    }

    @Override
    public synchronized void close() {
        super.close();
        if (instance != null)
            instance.close();
    }

    /**
     * get all Category
     * @return
     */
    public List<E_Category> getAllCategory() {
        List<E_Category> listCategories = new ArrayList<E_Category>();
        try {
            Cursor cursor = db.query(TB_CATEGORY, new String[] {
                    TB_CATEGORY_ID, TB_CATEGORY_NAME
            }, null, null, null, null, null);
            if (cursor.getCount() > 0)
                cursor.moveToFirst();
            do {
                listCategories.add(new E_Category(cursor.getInt(cursor
                        .getColumnIndexOrThrow(TB_CATEGORY_ID)), cursor.getString(cursor
                        .getColumnIndexOrThrow(TB_CATEGORY_NAME))));
            } while (cursor.moveToNext());
        } catch (Exception e) {
            Utils.TRACE("DatabaseHelper", "getAllCategory : " + e.getMessage());
        }
        return listCategories;
    }

    /**
     * method utama untuk pencarian database
     * 
     * @param category kategorinya
     * @param search kata kunci yang dicari
     * @param currentLocation location user
     * @return
     */
    public List<E_Toko> getToko(int category, String search, Location currentLocation) {
        List<E_Toko> listTokos = new ArrayList<E_Toko>();
        Cursor cursor;

        // ***** build search parameter *****

        // get all
        if (category == 0) {
            // if search empty otherwise get all location
            Utils.TRACE("DatabaseHelper", "getToko category 0");
            if (TextUtils.isEmpty(search)) {
                cursor = db.query(TB_TOKO, new String[] {
                        TB_TOKO_ID,
                        TB_TOKO_CATEGORY,
                        TB_TOKO_NAME,
                        TB_TOKO_ADDRESS,
                        TB_TOKO_LAT,
                        TB_TOKO_LNG,
                        TB_TOKO_PHONE
                }, null, null, null, null,
                        null);
            } else {
                // if search has some paramater
                // address and name
                cursor = db.query(TB_TOKO, new String[] {
                        TB_TOKO_ID,
                        TB_TOKO_CATEGORY,
                        TB_TOKO_NAME,
                        TB_TOKO_ADDRESS,
                        TB_TOKO_LAT,
                        TB_TOKO_LNG,
                        TB_TOKO_PHONE
                }, TB_TOKO_NAME + " LIKE '%"
                        + search
                        + "%'", null, null, null,
                        null);
            }
        } else {
            // has category
            if (TextUtils.isEmpty(search)) {
                // if search was empty
                // no parameter search, just get all location from category
                Utils.TRACE("DatabaseHelper", "getToko : search by category " + category);

                cursor = db.query(TB_TOKO, new String[] {
                        TB_TOKO_ID,
                        TB_TOKO_CATEGORY,
                        TB_TOKO_NAME,
                        TB_TOKO_ADDRESS,
                        TB_TOKO_LAT,
                        TB_TOKO_LNG,
                        TB_TOKO_PHONE
                }, TB_TOKO_CATEGORY + "=?", new String[] {
                        String.valueOf(category)
                }, null, null,
                        null);
            } else {
                // has parameter on search

                Utils.TRACE("DatabaseHelper", "getToko : search " + search + " category :"
                        + category);

                String sql = "SELECT * FROM " + TB_TOKO + " WHERE " + TB_TOKO_CATEGORY + "="
                        + category + " AND " + TB_TOKO_NAME
                        + " LIKE '%" + search + "%'";

                cursor = db.rawQuery(sql, null);
                Utils.TRACE("DatabaseHelper", "getToko : sql -> " + sql);
            }
        }

        // *** END Build Search ****
        // get cursor

        if (cursor.getCount() > 0) {
            cursor.moveToFirst();
            Utils.TRACE("DatabaseHelper", "getToko : data ada");

            do {
                double jarak = Utils.hitungJarak(new LatLng(currentLocation.getLatitude(),
                        currentLocation.getLongitude()),
                        new LatLng(cursor.getDouble(cursor.getColumnIndexOrThrow(TB_TOKO_LAT)),
                                cursor.getDouble(cursor.getColumnIndexOrThrow(TB_TOKO_LNG))));

                jarak = jarak / 1000;

                listTokos.add(new E_Toko(cursor.getInt(cursor.getColumnIndexOrThrow(TB_TOKO_ID)),
                        cursor.getInt(cursor.getColumnIndexOrThrow(TB_TOKO_CATEGORY)),
                        cursor.getString(cursor.getColumnIndexOrThrow(TB_TOKO_NAME)),
                        cursor.getString(cursor.getColumnIndexOrThrow(TB_TOKO_ADDRESS)),
                        cursor.getString(cursor.getColumnIndexOrThrow(TB_TOKO_PHONE)),
                        cursor.getDouble(cursor.getColumnIndexOrThrow(TB_TOKO_LAT)),
                        cursor.getDouble(cursor.getColumnIndexOrThrow(TB_TOKO_LNG)),
                        jarak));
            } while (cursor.moveToNext());
        }
        sortingLocation(listTokos);
        return listTokos;

    }

    /**
     * get single item toko
     * 
     * @param id
     * @return
     */
    public E_Toko getTokoById(int id) {
        Cursor cursor = db.query(TB_TOKO, new String[] {
                TB_TOKO_ID,
                TB_TOKO_CATEGORY,
                TB_TOKO_NAME,
                TB_TOKO_ADDRESS,
                TB_TOKO_LAT,
                TB_TOKO_LNG,
                TB_TOKO_PHONE
        }, TB_TOKO_ID + "=?", new String[] {
                String.valueOf(id)
        }, null, null, null);

        if (cursor.getCount() > 0) {
            cursor.moveToFirst();

            return new E_Toko(cursor.getInt(cursor.getColumnIndexOrThrow(TB_TOKO_ID)),
                    cursor.getInt(cursor.getColumnIndexOrThrow(TB_TOKO_CATEGORY)),
                    cursor.getString(cursor.getColumnIndexOrThrow(TB_TOKO_NAME)),
                    cursor.getString(cursor.getColumnIndexOrThrow(TB_TOKO_ADDRESS)),
                    cursor.getString(cursor.getColumnIndexOrThrow(TB_TOKO_PHONE)),
                    cursor.getDouble(cursor.getColumnIndexOrThrow(TB_TOKO_LAT)),
                    cursor.getDouble(cursor.getColumnIndexOrThrow(TB_TOKO_LNG)));
        } else {
            return null;
        }
    }

    /**
     * sorting
     * 
     * @param list
     */
    private static void sortingLocation(List<E_Toko> list)
    {
        Collections.sort(list, new Comparator<E_Toko>() {
            @Override
            public int compare(E_Toko lhs, E_Toko rhs)
            {
                return (lhs.getJarak() < rhs.getJarak() ? -1
                        : (rhs.getJarak() == lhs.getJarak() ? 0 : 1));
            }
        });
    }
}
